/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package MODEL.DAO;

import MODEL.POJO.ChiTietSanPhamPOJO;
import MODEL.UTIL.MySqlDataAccessHelper;
import com.mysql.jdbc.PreparedStatement;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author kijoo
 */
public class ChiTietSanPhamDAO {

    public static ArrayList<ChiTietSanPhamPOJO> LayDSTheoLDMCoPhanTrang(String idloaidanhmuc, int trang, String yeucau) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int soSanPhamTrenTrang = 9;
        int tinhtrang = 1;
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = null;
        if (yeucau != null) {
            if (yeucau.equals("Tang")) {
                sql = String.format("SELECT * "
                        + " FROM LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + " WHERE  LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + " LDM.IDLOAIDANHMUC ='%s' AND TINHTRANG='%d' ORDER BY CTSP.GIABAN ASC LIMIT %d,%d", idloaidanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("Giam")) {
                sql = String.format("SELECT * "
                        + " FROM LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + " WHERE  LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + " LDM.IDLOAIDANHMUC ='%s' AND TINHTRANG='%d' ORDER BY CTSP.GIABAN DESC LIMIT %d,%d", idloaidanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("Moi")) {
                sql = String.format("SELECT * "
                        + " FROM LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + " WHERE  LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + " LDM.IDLOAIDANHMUC ='%s' AND TINHTRANG='%d' ORDER BY CTSP.IDCHITIETSANPHAM DESC LIMIT %d,%d", idloaidanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("XemNhieu")) {
                sql = String.format("SELECT * "
                        + " FROM LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + " WHERE  LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + " LDM.IDLOAIDANHMUC ='%s' AND TINHTRANG='%d' ORDER BY CTSP.SOLANXEM DESC LIMIT %d,%d", idloaidanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
            }
        } else {
            sql = String.format("SELECT * "
                    + " FROM LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                    + " WHERE  LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                    + " LDM.IDLOAIDANHMUC ='%s' AND TINHTRANG='%d' LIMIT %d,%d", idloaidanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
        }
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LayDSTheoDMCoPhanTrang(String iddanhmuc, int trang, String yeucau) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int soSanPhamTrenTrang = 9;
        int tinhtrang = 1;
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = null;
        if (yeucau != null) {
            if (yeucau.equals("Tang")) {
                sql = String.format("SELECT * "
                        + " FROM DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + " WHERE  DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + " DM.IDDANHMUC ='%s' AND TINHTRANG='%d' ORDER BY CTSP.GIABAN ASC LIMIT %d,%d", iddanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("Giam")) {
                sql = String.format("SELECT * "
                        + " FROM DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + " WHERE  DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + " DM.IDDANHMUC ='%s' AND TINHTRANG='%d' ORDER BY CTSP.GIABAN DESC LIMIT %d,%d", iddanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("Moi")) {
                sql = String.format("SELECT * "
                        + " FROM DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + " WHERE  DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + " DM.IDDANHMUC ='%s' AND TINHTRANG='%d' ORDER BY CTSP.IDCHITIETSANPHAM DESC LIMIT %d,%d", iddanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("XemNhieu")) {
                sql = String.format("SELECT * "
                        + " FROM DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + " WHERE  DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + " DM.IDDANHMUC ='%s' AND TINHTRANG='%d' ORDER BY CTSP.SOLANXEM DESC LIMIT %d,%d", iddanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
            }
        } else {
            sql = String.format("SELECT * "
                    + " FROM DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                    + " WHERE  DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                    + " DM.IDDANHMUC ='%s' AND TINHTRANG='%d' LIMIT %d,%d", iddanhmuc, tinhtrang, batDau, soSanPhamTrenTrang);
        }
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LayDSTheoSPCoPhanTrang(String idsanpham, int trang, String yeucau) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int soSanPhamTrenTrang = 9;
        int tinhtrang = 1;
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        String sql = null;
        if (yeucau != null) {
            if (yeucau.equals("Tang")) {
                sql = String.format("SELECT * "
                        + " FROM CHITIETSANPHAM CTSP "
                        + " WHERE CTSP.IDSANPHAM ='%s' AND TINHTRANG='%d' ORDER BY CTSP.GIABAN ASC LIMIT %d,%d", idsanpham, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("Giam")) {
                sql = String.format("SELECT * "
                        + " FROM CHITIETSANPHAM CTSP "
                        + " WHERE CTSP.IDSANPHAM ='%s' AND TINHTRANG='%d' ORDER BY CTSP.GIABAN DESC LIMIT %d,%d", idsanpham, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("Moi")) {
                sql = String.format("SELECT * "
                        + " FROM CHITIETSANPHAM CTSP "
                        + " WHERE CTSP.IDSANPHAM ='%s' AND TINHTRANG='%d' ORDER BY CTSP.IDCHITIETSANPHAM DESC LIMIT %d,%d", idsanpham, tinhtrang, batDau, soSanPhamTrenTrang);
            } else if (yeucau.equals("XemNhieu")) {
                sql = String.format("SELECT * "
                        + " FROM CHITIETSANPHAM CTSP "
                        + " WHERE CTSP.IDSANPHAM ='%s' AND TINHTRANG='%d'ORDER BY CTSP.SOLANXEM DESC LIMIT %d,%d", idsanpham, tinhtrang, batDau, soSanPhamTrenTrang);
            }
        } else {
            sql = String.format("SELECT * "
                    + " FROM CHITIETSANPHAM CTSP "
                    + " WHERE CTSP.IDSANPHAM ='%s' AND TINHTRANG='%d' LIMIT %d,%d", idsanpham, tinhtrang, batDau, soSanPhamTrenTrang);
        }
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LayDanhSach() {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int tinhtrang = 1;
        String sql = String.format("SELECT * FROM chitietsanpham WHERE TINHTRANG='%d'", tinhtrang);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ChiTietSanPhamPOJO LayChiTietSanPham(int idchitietsanpham) {
        ChiTietSanPhamPOJO sp = new ChiTietSanPhamPOJO();
        int tinhtrang = 1;
        String sql = String.format("SELECT * FROM chitietsanpham WHERE IDCHITIETSANPHAM='%d' AND TINHTRANG='%d'", idchitietsanpham, tinhtrang);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                sp.setIdchitietsanpham(rs.getInt("idchitietsanpham"));
                sp.setIdsanpham(rs.getString("idsanpham"));
                sp.setTensanpham(rs.getString("tensanpham"));
                sp.setMota(rs.getString("mota"));
                sp.setHinh(rs.getString("hinh"));
                sp.setHinh1(rs.getString("hinh1"));
                sp.setGiaban(rs.getBigDecimal("giaban"));
                sp.setSoluongton(rs.getInt("soluongton"));
                sp.setSolanxem(rs.getInt("solanxem"));
                sp.setSolanmua(rs.getInt("solanmua"));
                sp.setBaohanh(rs.getInt("baohanh"));

            }
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return sp;
    }

    private static ArrayList<ChiTietSanPhamPOJO> LayDanhSachSanPham(ResultSet rs) throws SQLException {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        while (rs.next()) {
            ChiTietSanPhamPOJO sp = new ChiTietSanPhamPOJO();
            sp.setIdchitietsanpham(rs.getInt("idchitietsanpham"));
            sp.setIdsanpham(rs.getString("idsanpham"));
            sp.setTensanpham(rs.getString("tensanpham"));
            sp.setMota(rs.getString("mota"));
            sp.setHinh(rs.getString("hinh"));
            sp.setHinh1(rs.getString("hinh1"));
            sp.setGiaban(rs.getBigDecimal("giaban"));
            sp.setSoluongton(rs.getInt("soluongton"));
            sp.setSolanxem(rs.getInt("solanxem"));
            sp.setSolanmua(rs.getInt("solanmua"));
            sp.setBaohanh(rs.getInt("baohanh"));
            ds.add(sp);
        }
        return ds;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LaySanPhamMoiXuatHien(String idloaisanpham) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int soSanPhamTrenTrang = 9;
        int tinhtrang = 1;
        int batDau = 0;
        String sql = String.format("SELECT * "
                + " FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                + " WHERE LSP.IDLOAISANPHAM ='%s' AND LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM "
                + " AND TINHTRANG='%d' ORDER BY CTSP.IDCHITIETSANPHAM DESC LIMIT %d,%d", idloaisanpham, tinhtrang, batDau, soSanPhamTrenTrang);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LaySanPhamNoiBat(String idloaisanpham) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int soSanPhamTrenTrang = 9;
        int tinhtrang = 1;
        int batDau = 0;
        String sql = String.format("SELECT * "
                + " FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                + " WHERE LSP.IDLOAISANPHAM ='%s' AND LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM "
                + " AND TINHTRANG='%d' ORDER BY CTSP.SOLANXEM DESC LIMIT %d,%d ", idloaisanpham, tinhtrang, batDau, soSanPhamTrenTrang);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LaySanPhamBanChay(String idloaisanpham) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int soSanPhamTrenTrang = 9;
        int tinhtrang = 1;
        int batDau = 0;
        String sql = String.format("SELECT * "
                + " FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                + " WHERE LSP.IDLOAISANPHAM ='%s' AND LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM "
                + " AND TINHTRANG='%d' ORDER BY CTSP.SOLANMUA DESC LIMIT %d,%d ", idloaisanpham, tinhtrang, batDau, soSanPhamTrenTrang);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static boolean ThemChiTietSanPham(String maSP, String tenSP, String moTa, String hinh, String hinh2, BigDecimal giaBan, int soLuong, int baoHanh, String ngayNhap) {
        boolean kq = false;
        try {
            int tinhtrang = 1;
            int t = 0;
            String sql = String.format("INSERT INTO `nama`.`chitietsanpham` (`idsanpham`, `tensanpham`, `mota`, `hinh`, `hinh1`, `giaban`, `soluongton`, `tinhtrang`, `solanxem`, `solanmua`, `baohanh`, `ngaynhap`) VALUES ('%s', '%s', '%s', 'images/SanPham/%s', 'images/SanPham/%s', %f, %d, %d, %d, %d, %d, '%s')", maSP, tenSP, moTa, hinh, hinh2, giaBan, soLuong, tinhtrang, t, t, baoHanh, ngayNhap);
            //String sql = String.format("INSERT INTO CHITIETSANPHAM(IDSANPHAM, TENSANPHAM, MOTA, HINH, HINH1, GIABAN, SOLUONGTON, TINHTRANG, SOLANXEM, SOLANMUA, BAOHANH, NGAYNHAP) VALUES('%s', '%s', '%s', 'images/SanPham/%s', 'images/SanPham/%s', %f, %d, %d, %d, %d, %d, '%s' ) " , maSP, tenSP, moTa, hinh, hinh2, giaBan, soLuong, tinhtrang, t, t, baoHanh, ngayNhap);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            int n = helper.executeUpdate(sql);
            if (n == 1) {
                kq = true;
            }
            helper.Close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return kq;

    }

    public static boolean CapNhatSanPham(int idchitietSP, String tenSP, String moTa, String hinh, BigDecimal giaBan, int soLuong, int baoHanh) {
        boolean kq = false;
        try {

            String sql = String.format("UPDATE `nama`.`chitietsanpham` SET `tensanpham`= '%s', `mota`= '%s', `hinh`='images/SanPham/%s', `giaban`= %f, `soluongton`= %d, `baohanh`= %d WHERE `idchitietsanpham`= %d ", tenSP, moTa, hinh, giaBan, soLuong, baoHanh, idchitietSP);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            int n = helper.executeUpdate(sql);
            if (n == 1) {
                kq = true;
            }
            helper.Close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return kq;

    }

    public static boolean XoaSanPham(int idchitietsanpham) {
        boolean kq = false;
        try {
            int tinhtrang = 0;
            String sql = String.format("UPDATE `nama`.`chitietsanpham` SET `tinhtrang`= %d WHERE `idchitietsanpham`= %d ", tinhtrang, idchitietsanpham);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            int n = helper.executeUpdate(sql);
            if (n == 1) {
                kq = true;
            }
            helper.Close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return kq;

    }
    // phan trang voi loaidanhmuc

    public static int SoLuongSanPham1(String idloaidanhmuc) {
        int soluong = 0;
        int tinhtrang = 1;
        try {
            String sql = String.format("SELECT COUNT(*) As SOLUONG "
                    + " FROM LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                    + " WHERE  LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                    + " LDM.IDLOAIDANHMUC ='%s' AND TINHTRANG='%d'", idloaidanhmuc, tinhtrang);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soluong = rs.getInt("SOLUONG");
            }
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return soluong;
    }

    public static int tinhsoluongtrang1(String idloaidanhmuc) {
        int sotrang;
        int soluongsanphamtrentrang = 9;
        int soluong = SoLuongSanPham1(idloaidanhmuc);
        sotrang = soluong / soluongsanphamtrentrang;
        if (soluong % soluongsanphamtrentrang != 0) {
            sotrang++;
        }
        return sotrang;
    }
    // phan trang voi danhmuc        

    public static int SoLuongSanPham2(String iddanhmuc) {
        int soluong = 0;
        int tinhtrang = 1;
        try {
            String sql = String.format("SELECT COUNT(*) As SOLUONG "
                    + " FROM DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                    + " WHERE  DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                    + " DM.IDDANHMUC ='%s' AND TINHTRANG='%d'", iddanhmuc, tinhtrang);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soluong = rs.getInt("SOLUONG");
            }
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return soluong;
    }

    public static int tinhsoluongtrang2(String iddanhmuc) {
        int sotrang;
        int soluongsanphamtrentrang = 9;
        int soluong = SoLuongSanPham2(iddanhmuc);
        sotrang = soluong / soluongsanphamtrentrang;
        if (soluong % soluongsanphamtrentrang != 0) {
            sotrang++;
        }
        return sotrang;
    }
    //Phan trang voi sanpham

    public static int SoLuongSanPham(String idsanpham) {
        int soluong = 0;
        int tinhtrang = 1;
        try {
            String sql = String.format("SELECT COUNT(*) As SOLUONG "
                    + " FROM CHITIETSANPHAM CTSP "
                    + " WHERE CTSP.IDSANPHAM ='%s' AND TINHTRANG='%d'", idsanpham, tinhtrang);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soluong = rs.getInt("SOLUONG");
            }
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return soluong;
    }

    public static int tinhsoluongtrang3(String idsanpham) {
        int sotrang;
        int soluongsanphamtrentrang = 9;
        int soluong = SoLuongSanPham(idsanpham);
        sotrang = soluong / soluongsanphamtrentrang;
        if (soluong % soluongsanphamtrentrang != 0) {
            sotrang++;
        }
        return sotrang;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LocSPTheoGia(String idloaisanpham, BigDecimal giatu, BigDecimal giaden, int Trang, String yeucau) {
        ArrayList<ChiTietSanPhamPOJO> dsctsp = new ArrayList<ChiTietSanPhamPOJO>();
        int sosanphamtrentrang = 9;
        int batdau = (Trang - 1) * sosanphamtrentrang;
        String sql = null;
        if (yeucau != null) {
            if (yeucau.equals("Tang")) {
                sql = String.format("Select CTSP.* "
                        + "FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + "WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + "LSP.IDLOAISANPHAM ='%s' AND TINHTRANG='1' AND giaban between '%f' and '%f' ORDER BY CTSP.GIABAN ASC LIMIT %d,%d", idloaisanpham, giatu, giaden, batdau, sosanphamtrentrang);
            } else if (yeucau.equals("Giam")) {
                sql = String.format("Select CTSP.* "
                        + "FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + "WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + "LSP.IDLOAISANPHAM ='%s' AND TINHTRANG='1' AND giaban between '%f' and '%f' ORDER BY CTSP.GIABAN DESC LIMIT %d,%d", idloaisanpham, giatu, giaden, batdau, sosanphamtrentrang);
            } else if (yeucau.equals("Moi")) {
                sql = String.format("Select CTSP.* "
                        + "FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + "WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + "LSP.IDLOAISANPHAM ='%s' AND TINHTRANG='1' AND giaban between '%f' and '%f' ORDER BY CTSP.IDCHITIETSANPHAM DESC LIMIT %d,%d", idloaisanpham, giatu, giaden, batdau, sosanphamtrentrang);
            } else if (yeucau.equals("XemNhieu")) {
                sql = String.format("Select CTSP.* "
                        + "FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                        + "WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                        + "LSP.IDLOAISANPHAM ='%s' AND TINHTRANG='1' AND giaban between '%f' and '%f' ORDER BY CTSP.SOLANXEM DESC LIMIT %d,%d", idloaisanpham, giatu, giaden, batdau, sosanphamtrentrang);
            }
        } else {
            sql = String.format("Select CTSP.* "
                    + "FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                    + "WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                    + "LSP.IDLOAISANPHAM ='%s' AND TINHTRANG='1' AND giaban between '%f' and '%f' LIMIT %d,%d ", idloaisanpham, giatu, giaden, batdau, sosanphamtrentrang);
        }
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            dsctsp = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return dsctsp;
    }

    public static int SoLuongSanPham4(String idloaisanpham, BigDecimal giatu, BigDecimal giaden) {
        int soluong = 0;
        String sql = String.format("Select COUNT(*) AS SOLUONG "
                + "FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                + "WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                + "LSP.IDLOAISANPHAM ='%s' AND TINHTRANG='1' AND giaban between '%f' and '%f'", idloaisanpham, giatu, giaden);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soluong = rs.getInt("SOLUONG");
            }
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return soluong;
    }

    public static int tinhsoluongtrang4(String idloaisanpham, BigDecimal giatu, BigDecimal giaden) {
        int sotrang;
        int soluongsanphamtrentrang = 9;
        int soluong = SoLuongSanPham4(idloaisanpham, giatu, giaden);
        sotrang = soluong / soluongsanphamtrentrang;
        if (soluong % soluongsanphamtrentrang != 0) {
            sotrang++;
        }
        return sotrang;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LayDSTheoLSPCoPhanTrang(String idloaisanpham, int Trang, String yeucau) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int tinhtrang = 1;
        int sosanphamtrentrang = 9;
        int batdau = (Trang - 1) * sosanphamtrentrang;

        String sql = String.format("SELECT CTSP.* "
                + " FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                + " WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                + " LSP.IDLOAISANPHAM ='%s' AND TINHTRANG='%d' LIMIT %d,%d", idloaisanpham, tinhtrang, batdau, sosanphamtrentrang);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static int SoLuongSanPham5(String idloaisanpham) {
        int soluong = 0;
        String sql = String.format("Select COUNT(*) AS SOLUONG "
                + " FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                + " WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                + " LSP.IDLOAISANPHAM ='%s' AND TINHTRANG='1'", idloaisanpham);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soluong = rs.getInt("SOLUONG");
            }
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return soluong;
    }

    public static int tinhsoluongtrang5(String idloaisanpham) {
        int sotrang;
        int soluongsanphamtrentrang = 9;
        int soluong = SoLuongSanPham5(idloaisanpham);
        sotrang = soluong / soluongsanphamtrentrang;
        if (soluong % soluongsanphamtrentrang != 0) {
            sotrang++;
        }
        return sotrang;
    }

    public static ArrayList<ChiTietSanPhamPOJO> TimKiemSanPhamTheoTen(String idloaisanpham, String tensanpham, int Trang) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int tinhtrang = 1;
        int sosanphamtrentrang = 9;
        int batdau = (Trang - 1) * sosanphamtrentrang;
        String sql = String.format("SELECT CTSP.* "
                + " FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                + " WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM "
                + " AND TINHTRANG='%d' AND CTSP.TENSANPHAM LIKE '%%%s%%' AND LSP.IDLOAISANPHAM = '%s' LIMIT %d,%d", tinhtrang, tensanpham, idloaisanpham, batdau, sosanphamtrentrang);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static int SoLuongSanPham6(String idloaisanpham, String tensanpham) {
        int soluong = 0;
        int tinhtrang = 1;
        String sql = String.format("Select COUNT(*) AS SOLUONG "
                + " FROM LOAISANPHAM LSP,LOAIDANHMUC LDM,DANHMUC DM,SANPHAM SP, CHITIETSANPHAM CTSP "
                + " WHERE  LSP.IDLOAISANPHAM=LDM.IDLOAISANPHAM AND LDM.IDLOAIDANHMUC=DM.IDLOAIDANHMUC AND DM.IDDANHMUC=SP.IDDANHMUC AND SP.IDSANPHAM=CTSP.IDSANPHAM AND "
                + " TINHTRANG='%d' AND CTSP.TENSANPHAM LIKE '%%%s%%' AND LSP.IDLOAISANPHAM = '%s'", tinhtrang, tensanpham, idloaisanpham);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soluong = rs.getInt("SOLUONG");
            }
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return soluong;
    }

    public static int tinhsoluongtrang6(String idloaisanpham, String tensanpham) {
        int sotrang;
        int soluongsanphamtrentrang = 9;
        int soluong = SoLuongSanPham6(idloaisanpham, tensanpham);
        sotrang = soluong / soluongsanphamtrentrang;
        if (soluong % soluongsanphamtrentrang != 0) {
            sotrang++;
        }
        return sotrang;
    }

    public static ArrayList<ChiTietSanPhamPOJO> LaySanPhamCungLoai(String idloaidanhmuc) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        String sql = String.format("select ctsp.* "
                + " from danhmuc dm, loaidanhmuc ldm, sanpham sp, chitietsanpham ctsp "
                + " where ldm.idloaidanhmuc=dm.idloaidanhmuc and dm.iddanhmuc=sp.iddanhmuc and sp.idsanpham=ctsp.idsanpham and ldm.idloaidanhmuc='%s' ", idloaidanhmuc);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    
    public static ArrayList<ChiTietSanPhamPOJO> LaySanPhamCungLoaiTheoIDDanhMuc(String iddanhmuc) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        String sql = String.format("select ctsp.* "
                + " from danhmuc dm, sanpham sp, chitietsanpham ctsp "
                + " where dm.iddanhmuc=sp.iddanhmuc and sp.idsanpham=ctsp.idsanpham and dm.iddanhmuc='%s' ", iddanhmuc);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    
    public static ArrayList<ChiTietSanPhamPOJO> LayDanhSachSanPhamTheoIDLoaiSanPham(String idloaisanpham, int Trang) {
        ArrayList<ChiTietSanPhamPOJO> ds = new ArrayList<ChiTietSanPhamPOJO>();
        int tinhtrang = 1;
        int sosanphamtrentrang = 9;
        int batdau = (Trang - 1) * sosanphamtrentrang;
        String sql = String.format("select ctsp.* "
                + " from loaisanpham lsp, danhmuc dm, loaidanhmuc ldm, sanpham sp, chitietsanpham ctsp "
                + "where lsp.idloaisanpham=ldm.idloaisanpham and ldm.idloaidanhmuc=dm.idloaidanhmuc and dm.iddanhmuc=sp.iddanhmuc and sp.idsanpham=ctsp.idsanpham and lsp.idloaisanpham='%s' and tinhtrang = %d limit %d, %d", idloaisanpham, tinhtrang, batdau, sosanphamtrentrang);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.executeQuery(sql);
            ds = LayDanhSachSanPham(rs);
            helper.Close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static boolean ThemSoLanMua(int idchitietsanpham, int solanmua) {
        boolean kq = false;
        try {
            int tinhtrang = 1;
            String sql = String.format("UPDATE `nama`.`chitietsanpham` SET `solanmua`=%d WHERE `idchitietsanpham`='%d'", solanmua,idchitietsanpham);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            int n = helper.executeUpdate(sql);
            if (n == 1) {
                kq = true;
            }
            helper.Close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return kq;
    }

    public static boolean ThemSoLanXem(int idchitietsanpham, int solanxem) {
        boolean kq = false;
        try {
            String sql = String.format("UPDATE `nama`.`chitietsanpham` SET `solanxem`=%d WHERE `idchitietsanpham`='%d'", solanxem,idchitietsanpham);
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.Open();
            int n = helper.executeUpdate(sql);
            if (n == 1) {
                kq = true;
            }
            helper.Close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return kq;
    }
}
